Skip Headers

Oracle® Database Application Developer's Guide - Object-Relational Features 10g Release 2 (10.2) Part Number B14260-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 3 Support for Collection Datatypes

This chapter provides basic information about working with varrays and nested table collection datatypes. It explains how to create and manage collection datatypes.

This chapter contains these topics:

- Creating Collection Datatypes

- Operations on Collection Datatypes

## Creating Collection Datatypes

Oracle supports the varray and nested table collection datatypes.

- A varray is an ordered collection of elements

- A nested table can have any number of elements

If you need to store only a fixed number of items, or to loop through the elements in order, or you will often want to retrieve and manipulate the entire collection as a value, then use a varray.

If you need to run efficient queries on a collection, handle arbitrary numbers of elements, or perform mass insert, update, or delete operations, then use a nested table. See "Design Considerations for Collections".

### Creating an Instance of a VARRAY or Nested Table

You create an instance of a collection type in the same way that you create an instance of any other object type, namely, by calling the type's constructor method. The name of a type's constructor method is simply the name of the type. You specify the elements of the collection as a comma-delimited list of arguments to the method.

Calling a constructor method with an empty list creates an empty collection of that type. Note that an empty collection is an actual collection that happens to be empty; it is not the same as a null collection. See "Design Considerations for Nested Tables" for more information on using nested tables.

### Constructor Methods for Collections

Example 3-1 shows how a literal invocation of the constructor method is used in a SQL statement to insert values into a nested table type `people_typ`.

Example 3-1 Using the Constructor Method to Insert Values into a Nested Table

    CREATE TYPE people_typ AS TABLE OF person_typ;
    /
    CREATE TABLE people_tab (
        group_no NUMBER,
        people_column people_typ )
        NESTED TABLE people_column STORE AS people_column_nt;

    INSERT INTO people_tab VALUES (
                100,
                people_typ( person_typ(1, 'John Smith', '1-800-555-1212'),
                            person_typ(2, 'Diane Smith', NULL)));

When you declare a table column to be of an object type or collection type, you can include a `DEFAULT` clause. This provides a value to use in cases where you do not explicitly specify a value for the column. The `DEFAULT` clause must contain a literal invocation of the constructor method for that object or collection.

Example 3-2 shows how to use literal invocations of constructor methods to specify defaults for `person_typ` and `people_typ`:

Example 3-2 Creating the department_persons Table Using the DEFAULT Clause

    CREATE TABLE department_persons (
      dept_no    NUMBER PRIMARY KEY,
      dept_name  CHAR(20),
      dept_mgr   person_typ DEFAULT person_typ(10,'John Doe',NULL),
      dept_emps  people_typ DEFAULT people_typ() )
      NESTED TABLE dept_emps STORE AS dept_emps_tab;

    INSERT INTO department_persons VALUES
       ( 101, 'Physical Sciences', person_typ(65,'Vrinda Mills', '1-800-555-4412'),
               people_typ( person_typ(1, 'John Smith', '1-800-555-1212'),
                            person_typ(2, 'Diane Smith', NULL) ) );
    INSERT INTO department_persons VALUES
      ( 104, 'Life Sciences', person_typ(70,'James Hall', '1-800-555-4621'),
        people_typ() );

Note that the term `people_typ()` is a literal invocation of the constructor method for an empty `people_typ` table.

### Varrays

A varray is an ordered set of data elements. All elements of a given varray are of the same datatype or a subtype of the declared one. Each element has an index, which is a number corresponding to the element's position in the array. The index number is used to access a specific element.

When you define a varray, you specify the maximum number of elements it can contain, although you can change this number later. The number of elements in an array is the size of the array. Oracle allows arrays to be of variable size, which is why they are called varrays.

The following statement creates an array type `email_list_arr` that has no more than ten elements, each of datatype `VARCHAR2(80)`.

    CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);
    /

In Example 3-3, a `VARRAY` type is created that is an array of an object type. The `phone_varray_typ` `VARRAY` type is used as a datatype for a column in the `dept_phone_list` table. The `INSERT` statements show how to insert values into `phone_varray_typ` in the table.

Example 3-3 Creating and Populating a VARRAY Datatype

    CREATE TYPE phone_typ AS OBJECT (
        country_code   VARCHAR2(2),
        area_code      VARCHAR2(3),
        ph_number      VARCHAR2(7));
    /
    CREATE TYPE phone_varray_typ AS VARRAY(5) OF phone_typ;
    /
    CREATE TABLE dept_phone_list (
      dept_no NUMBER(5),
      phone_list phone_varray_typ);

    INSERT INTO dept_phone_list VALUES (
       100,
       phone_varray_typ( phone_typ ('01', '650', '5061111'),
                          phone_typ ('01', '650', '5062222'),
                          phone_typ ('01', '650', '5062525')));

Creating an array type, as with a SQL object type, does not allocate space. It defines a datatype, which you can use as:

- The datatype of a column of a relational table.

- An object type attribute.

- The type of a PL/SQL variable, parameter, or function return value.

A varray is normally stored in line, that is, in the same tablespace as the other data in its row. If it is sufficiently large, Oracle stores it as a `BLOB`. See "Storage Considerations for Varrays".

You can create a `VARRAY` type of `XMLType` or of a `LOB` type for procedural purposes, such as in PL/SQL or in view queries. However, database storage of a varray of those types is not supported. This means that you cannot create an object table or an object type column of a varray type of `XMLType` or of a `LOB` type.

See Also: Oracle Database SQL Reference for information and examples on the `STORE` `AS` `LOB` clause of the `CREATE` `TABLE` statement

### Nested Tables

A nested table is an unordered set of data elements, all of the same datatype. No maximum is specified in the definition of the table and the order of the elements is not preserved. You select, insert, delete, and update in a nested table just as you do with ordinary tables using the TABLE expression.

Elements of a nested table are actually stored in a separate storage table that contains a column that identifies the parent table row or object to which each element belongs. A nested table has a single column, and the type of that column is a built-in type or an object type. If the column in a nested table is an object type, the table can also be viewed as a multi-column table, with a column for each attribute of the object type.

In Example 3-4, the table type used for the nested tables is declared with the `CREATE` `TYPE` ... `IS` `TABLE` `OF` statement. A table type definition does not allocate space. It defines a type, which you can use as:

- The datatype of a column of a relational table

- An object type attribute

- A PL/SQL variable, parameter, or function return type

When a column in a relational table is of nested table type, Oracle stores the nested table data for all rows of the relational table in the same storage table. Similarly, with an object table of a type that has a nested table attribute, Oracle stores nested table data for all object instances in a single storage table associated with the object table. See Figure 8-2, "Nested Table Storage".

In Example 3-4, the `NESTED` `TABLE` clause specifies the storage name for the nested table. The storage name is used when creating an index on a nested table. The example uses `person_typ` defined in Example 2-1 and people_typ defined in Example 3-1.

Example 3-4 Creating and Populating Simple Nested Tables

    CREATE TABLE students (
       graduation DATE,
       math_majors people_typ,
       chem_majors people_typ,
       physics_majors people_typ)
      NESTED TABLE math_majors STORE AS math_majors_nt
      NESTED TABLE chem_majors STORE AS chem_majors_nt
      NESTED TABLE physics_majors STORE AS physics_majors_nt;

    CREATE INDEX math_idno_idx ON math_majors_nt(idno);
    CREATE INDEX chem_idno_idx ON chem_majors_nt(idno);
    CREATE INDEX physics_idno_idx ON physics_majors_nt(idno);

    INSERT INTO students (graduation) VALUES ('01-JUN-03');
    UPDATE students
      SET math_majors =
            people_typ (person_typ(12, 'Bob Jones', '111-555-1212'),
                        person_typ(31, 'Sarah Chen', '111-555-2212'),
                        person_typ(45, 'Chris Woods', '111-555-1213')),
          chem_majors =
            people_typ (person_typ(51, 'Joe Lane', '111-555-1312'),
                        person_typ(31, 'Sarah Chen', '111-555-2212'),
                        person_typ(52, 'Kim Patel', '111-555-1232')),
       physics_majors =
            people_typ (person_typ(12, 'Bob Jones', '111-555-1212'),
                        person_typ(45, 'Chris Woods', '111-555-1213'))
    WHERE graduation = '01-JUN-03';

A convenient way to access the elements of a nested table individually is to use a nested cursor or the `TABLE` function. See "Querying Collections".

### Specifying a Tablespace When Storing a Nested Table

A nested table can be stored in a different tablespace than its parent table. In Example 3-5, the nested table is stored in the `system` tablespace:

Example 3-5 Specifying a Different Tablespace for Storing a Nested Table

    CREATE TABLE people_tab (
        people_column people_typ )
        NESTED TABLE people_column STORE AS people_column_nt (TABLESPACE system);

If the `TABLESPACE` clause is not specified, then the storage table of the nested table is created in the tablespace where the parent table is created. For multilevel nested tables, Oracle creates the child table in the same tablespace as its immediate preceding parent table.

The user can issue `ALTER` `TABLE` `MOVE` statement to move a table to a different tablespace. If the user issues `ALTER` `TABLE` `MOVE` statement on a table with nested table columns, it only moves parent table, no action is taken on the nested table's storage tables. If the user wants to move a nested table s storage table to a different tablespace, issue `ALTER` `TABLE` `MOVE` on the storage table. For example:

    ALTER TABLE people_tab MOVE TABLESPACE system;
    ALTER TABLE people_column_nt MOVE TABLESPACE example;

Now the `people_tab table` is in `system` tablespace and nested table is stored in the `example` tablespace.

### Varray Storage

Multilevel varrays are stored in one of two ways, depending on whether the varray is a varray of varrays or a varray of nested tables.

- In a varray of varrays, the entire varray is stored inline in the row unless it is larger than approximately 4000 bytes or `LOB` storage is explicitly specified.

- In a varray of nested tables, the entire varray is stored in a `LOB`, with only the `LOB` locator stored in the row. There is no storage table associated with nested table elements of a varray. The entire nested table collection is stored inside the varray.

You can explicitly specify `LOB` storage for varrays. The following example does this for the varray elements of a nested table. As Example 3-6 also shows, you can use the `COLUMN_VALUE` keyword with varrays as well as nested tables.

Example 3-6 Specifying LOB Storage for a VARRAY Elements of a Nested Table

    CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);
    /

    CREATE TYPE email_list_typ AS TABLE OF email_list_arr;
    /

    CREATE TABLE dept_email_list (
      dept_no NUMBER,
      email_addrs email_list_typ)
      NESTED TABLE email_addrs STORE AS email_addrs_nt
      (VARRAY COLUMN_VALUE STORE AS LOB dept_emails_lob);

Example 3-7 shows explicit `LOB` storage specified for a varray of varray type.

Example 3-7 Specifying LOB Storage for a VARRAY Type

    CREATE TYPE email_list_typ2 AS OBJECT (
        section_no   NUMBER,
        emails       email_list_arr);
    /

    CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ2;
    /

    CREATE TABLE dept_email_list2 (
      dept_no NUMBER,
      email_addrs email_varray_typ)
      VARRAY email_addrs STORE AS LOB dept_emails_lob2;

See "Storage Considerations for Varrays". See also Oracle Database Application Developer's Guide - Large Objects.

### Increasing the Size and Precision of VARRAYs and Nested Tables

When the element type of a `VARRAY` type is a variable character or RAW type or a numeric type, you can increase the size of the variable character or RAW type or increase the precision of the numeric type. A new type version is generated for the `VARRAY` type. The same changes can be applied to nested table types.

Options like `INVALIDATE` and `CASCADE` are provided to either invalidate all dependent objects or propagate the change to its type and table dependents.

Example 3-8 is illustrates the use `ALTER` `TYPE` to increase the size of a `VARRAY` and a nested table element type.

Example 3-8 Increasing the Size of an Element Type in a VARRAY and Nested Table

    CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);
    /
    ALTER TYPE email_list_arr MODIFY ELEMENT TYPE VARCHAR2(100) CASCADE;

    CREATE TYPE email_list_tab AS TABLE OF VARCHAR2(30);
    /
    ALTER TYPE email_list_tab MODIFY ELEMENT TYPE VARCHAR2(40) CASCADE;

### Increasing VARRAY Limit Size

The `ALTER` `TYPE` ... `MODIFY` `LIMIT` syntax allows increasing the number of elements of a `VARRAY` type. If the number of elements of the `VARRAY` type is increased, a new type version is generated for the `VARRAY` type and this is maintained as part of the history of the type changes.

Options like `INVALIDATE` and `CASCADE` are provided to either invalidate all dependent objects or propagate the change to its type and table dependents.

Example 3-9 Increasing the VARRAY Limit Size

    CREATE TYPE email_list_arr AS VARRAY(10) OF VARCHAR2(80);
    /
    CREATE TYPE email_list_typ AS OBJECT (
        section_no   NUMBER,
        emails       email_list_arr);
    /

    CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ;
    /

    ALTER TYPE email_varray_typ MODIFY LIMIT 100 INVALIDATE;

When a `VARRAY` type is altered, changes are propagated to the dependent tables. See "Propagating VARRAY Size Change".

### Creating a Varray Containing LOB References

In Example 3-10, `email_addrs` of type `email_list_typ` already exists in table `dept_email_list` as shown in the SQL examples in "Varray Storage".

To create a varray of LOB references, first define a `VARRAY` type of type `REF` `email_list_typ`2. Next define a column of the array type in `dept_email_list3`.

Example 3-10 Creating a VARRY Containing LOB References

    CREATE TYPE ref_email_varray_typ AS VARRAY(5) OF REF email_list_typ;
    /

    CREATE TABLE dept_email_list3 (
      dept_no NUMBER,
      email_addrs ref_email_varray_typ)
      VARRAY email_addrs STORE AS LOB dept_emails_lob3;

### Multilevel Collection Types

Multilevel collection types are collection types whose elements are themselves directly or indirectly another collection type. Possible multilevel collection types are:

- Nested table of nested table type

- Nested table of varray type

- Varray of nested table type

- Varray of varray type

- Nested table or varray of a user-defined type that has an attribute that is a nested table or varray type

Like ordinary, single-level collection types, multilevel collection types can be used as columns in a relational table or with object attributes in an object table.

#### Nested Table Storage Tables for Multilevel Collection Types

A nested table type column or object table attribute requires a storage table where rows for all nested tables in the column are stored. With a multilevel nested table collection of nested tables, the inner set of nested tables also requires a storage table just as the outer set does. You specify one by appending a second nested-table storage clause.

Example 3-11 creates a multilevel collection type that is a nested table of nested tables. The example models a system of corporate regions in which each region has a nested table collection of the countries, and each country has a nested table collection of its locations. This example is based on the `regions`, `countries`, and `locations` tables of the Oracle `HR` sample schema.

In Example 3-11, the SQL statements create a table `region_tab` that contains a column `countries` whose type is a multilevel collection. This multilevel collection is a nested table of an object type that has a nested table attribute `locations`. Separate nested table clauses are provided for the outer `countries` nested table and for the inner `locations` one.

Example 3-11 Multilevel Nested Table Storage

    CREATE TYPE location_typ AS OBJECT (
      location_id      NUMBER(4),
      street_address   VARCHAR2(40),
      postal_code      VARCHAR2(12),
      city             VARCHAR2(30),
      state_province   VARCHAR2(25));
    /

    CREATE TYPE nt_location_typ AS TABLE OF location_typ;
    /

    CREATE TYPE country_typ AS OBJECT (
      country_id     CHAR(2),
      country_name   VARCHAR2(40),
      locations      nt_location_typ);
    /

    CREATE TYPE nt_country_typ AS TABLE OF country_typ;
    /

    CREATE TABLE region_tab (
      region_id     NUMBER,
      region_name   VARCHAR2(25),
      countries     nt_country_typ)
      NESTED TABLE countries STORE AS nt_countries_tab
       (NESTED TABLE locations STORE AS nt_locations_tab);

In Example 3-11 you can refer to the inner `locations` nested table by name because this nested table is a named attribute of an object. However, if the inner nested table is not an attribute, it has no name. The keyword `COLUMN_VALUE` is provided for this case; you use it in place of a name for an inner nested table as shown in Example 3-12.

Example 3-12 Multilevel Nested Table Storage Using the COLUMN_VALUE Keyword

    CREATE TYPE inner_table AS TABLE OF NUMBER;
    /
    CREATE TYPE outer_table AS TABLE OF inner_table;
    /
    CREATE TABLE tab1 (
      col1 NUMBER,
      col2 outer_table)
    NESTED TABLE col2 STORE AS col2_ntab
      (NESTED TABLE COLUMN_VALUE STORE AS cv_ntab);

Physical attributes for the storage tables can be specified in the nested table clause, as shown in Example 3-13.

Example 3-13 Specifying Physical Attributes for Nested Table Storage

    CREATE TABLE region_tab (
      region_id     NUMBER,
      region_name   VARCHAR2(25),
      countries     nt_country_typ)
      NESTED TABLE countries STORE AS nt_countries_tab (
       (PRIMARY KEY (NESTED_TABLE_ID, country_id))
       ORGANIZATION INDEX COMPRESS
       NESTED TABLE locations STORE AS nt_locations_tab);

Every nested table storage table contains a column, referenceable by `NESTED_TABLE_ID`, that keys rows in the storage table to the associated row in the parent table. A parent table that is itself a nested table has two system-supplied ID columns: one, referenceable by `NESTED_TABLE_ID`, that keys its rows back to rows in its own parent table, and one hidden column referenced by the `NESTED_TABLE_ID` column in its nested table children.

In Example 3-13, nested table `countries` is made an index-organized table (IOT) by adding the `ORGANIZATION` `INDEX` clause and assigning the nested table a primary key in which the first column is `NESTED_TABLE_ID`. This column contains the ID of the row in the parent table with which a storage table row is associated. Specifying a primary key with `NESTED_TABLE_ID` as the first column and index-organizing the table cause Oracle to physically cluster all the nested table rows that belong to the same parent row, for more efficient access.

Each nested table needs its own table storage clause, so you must have as many nested table storage clauses as you have levels of nested tables in a collection. See "Nested Table Storage".

#### Assignment and Comparison of Multilevel Collections

As with single-level collections, both the source and the target must be of the same declared data type in assignments of multilevel collections.

Only items whose datatypes are nested table collection types, including multilevel collection types, can be compared. See "Comparisons of Collections".

#### Constructors for Multilevel Collections

Like single-level collection types, multilevel collection types are created by calling the respective type's constructor method. Like the constructor methods for other object types, a constructor for a multilevel collection type is a system-defined function that has the same name as the type and returns a new instance of it—in this case, a new multilevel collection. Constructor parameters have the names and types of the object type's attributes.

Example 3-14 shows the constructor call for the multilevel collection type `nt_country_typ`. This type is a nested table of countries, each of which contains a nested table of locations as an attribute. The constructor for the outer nested table calls the `country_typ` constructor for each country to be created; each country constructor calls the constructor for the locations nested table type to create its nested table of locations; and the locations nested table type constructor calls the `location_typ` constructor for each location instance to be created.

Example 3-14 Using Constructors for Multilevel Collections

    INSERT INTO region_tab
    VALUES(1, 'Europe', nt_country_typ(
      country_typ( 'IT', 'Italy', nt_location_typ (
        location_typ(1000, '1297 Via Cola di Rie','00989','Roma', ''),
        location_typ(1100, '93091 Calle della Testa','10934','Venice','') )
        ),
      country_typ( 'CH', 'Switzerland', nt_location_typ (
        location_typ(2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve'),
        location_typ(3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE') )
        ),
      country_typ( 'UK', 'United Kingdom', nt_location_typ (
        location_typ(2400, '8204 Arthur St', '', 'London', 'London'),
        location_typ(2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB',
                     'Oxford', 'Oxford'),
        location_typ(2600, '9702 Chester Road', '09629850293', 'Stretford',
                     'Manchester') )
          )
      )
    );

## Operations on Collection Datatypes

This section describes the operations on collection datatypes.

### Querying Collections

There are two general ways to query a table that contains a column or attribute of a collection type. One way returns the collections nested in the result rows that contain them. The other way distributes or unnests collections such that each collection element appears on a row by itself.

#### Nesting Results of Collection Queries

The following queries use the `department_persons` table shown in Example 3-2. The column `dept_emps` is a nested table collection of `person_typ` type. The `dept_emps` collection column appears in the `SELECT` list like an ordinary, scalar column. Querying a collection column in the `SELECT` list like this nests the elements of the collection in the result row with which the collection is associated.

In Example 3-15 the query retrieves the nested collection of employees.

Example 3-15 Nesting Results of Collection Queries

    SELECT d.dept_emps
      FROM department_persons d;

`DEPT_EMPS(IDNO, NAME, PHONE)`
`-------------------------------------------------------------`
`PEOPLE_TYP(PERSON_TYP(1, 'John Smith', '1-800-555-1212'),`
`PERSON_TYP(2, 'Diane Smith', '1-800-555-1243'))`

The results are also nested if an object-type column in the `SELECT` list contains a collection attribute, even if that collection is not explicitly listed in the `SELECT` list itself. For example, the query `SELECT` `*` `FROM` `department_persons` would produce a nested result.

#### Unnesting Results of Collection Queries

Not all tools or applications are able to deal with results in a nested format. To view Oracle collection data using tools that require a conventional format, you must unnest, or flatten, the collection attribute of a row into one or more relational rows. You can do this by using a `TABLE` expression with the collection. A `TABLE` expression enables you to query a collection in the `FROM` clause like a table. In effect, you join the nested table with the row that contains the nested table.

The `TABLE` expression can be used to query any collection value expression, including transient values such as variables and parameters.

As in Example 3-15, the query in Example 3-16 retrieves the collection of employees, but the collection is unnested.

Example 3-16 Unnesting Results of Collection Queries

    SELECT e.*
      FROM department_persons d, TABLE(d.dept_emps) e;

`IDNO NAME PHONE`
`---------- ------------------------------ ---------------`
`1 John Smith 1-800-555-1212`
`2 Diane Smith 1-800-555-1243`

As shown in Example 3-16, a `TABLE` expression can have its own table alias. In the example, a table alias for the `TABLE` expression appears in the `SELECT` list to select columns returned by the `TABLE` expression.

The `TABLE` expression uses another table alias to specify the table that contains the collection column that the `TABLE` expression references. The expression `TABLE(d.dept_emps)` specifies the `department_persons` table as containing the `dept_emps` collection column. A `TABLE` expression can use the table alias of any table appearing to the left of it in a `FROM` clause to reference a column of that table. This way of referencing collection columns is called left correlation.

In the example, the `department_persons` table is listed in the `FROM` clause solely to provide a table alias for the `TABLE` expression to use. No columns from the `department_persons` table other than the column referenced by the `TABLE` expression appear in the result

The following example produces rows only for departments that have employees.

    SELECT d.dept_no, e.*
      FROM department_persons d, TABLE(d.dept_emps) e;

To get rows for departments that have no employees, you can use outer-join syntax:

    SELECT d.dept_no, e.*
      FROM department_persons d, TABLE(d.dept_emps) (+) e;

The (+) indicates that the dependent join between `department_persons` and `e.dept_emps` should be `NULL`-augmented. That is, there will be rows of `department_persons` in the output for which `e.dept_emps` is `NULL` or empty, with `NULL` values for columns corresponding to `e.dept_emps`.

#### Unnesting Queries Containing Table Expression Subqueries

The examples in "Unnesting Results of Collection Queries" show a `TABLE` expression that contains the name of a collection. Alternatively, a `TABLE` expression can contain a subquery of a collection.

Example 3-17 returns the collection of employees whose department number is `101`.

Example 3-17 Using a Table Expression Containing a Subquery of a Collection

    SELECT *
      FROM TABLE(SELECT d.dept_emps
                   FROM department_persons d
                   WHERE d.dept_no = 101);

There are these restrictions on using a subquery in a `TABLE` expression:

- The subquery must return a collection type

- The `SELECT` list of the subquery must contain exactly one item

- The subquery must return only a single collection; it cannot return collections for multiple rows. For example, the subquery `SELECT` `dept_emps` `FROM` `department_persons` succeeds in a `TABLE` expression only if table `department_persons` contains just a single row. If the table contains more than one row, the subquery produces an error.

Example 3-18 shows a `TABLE` expression used in the `FROM` clause of a `SELECT` embedded in a `CURSOR` expression.

Example 3-18 Using a Table Expression in a CURSOR Expression

    SELECT d.dept_no, CURSOR(SELECT * FROM TABLE(d.dept_emps))
      FROM department_persons d
       WHERE d.dept_no = 101;

#### Unnesting Queries with Multilevel Collections

Unnesting queries can be used with multilevel collections, too, for both varrays and nested tables. Example 3-19 shows an unnesting query on a multilevel nested table collection of nested tables. From a table `region_tab` in which each region has a nested table of countries and each country has a nested table of locations, the query returns the names of all regions, countries, and locations.

Example 3-19 Unnesting Queries with Multilevel Collections Using the TABLE Function

    SELECT r.region_name, c.country_name, l.location_id
      FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l;

    -- the following query is optimized to run against the locations table
    SELECT l.location_id, l.city
      FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l;

Because no columns of the base table `region_tab` appear in the second `SELECT` list, the query is optimized to run directly against the `locations` storage table.

Outer-join syntax can also be used with queries of multilevel collections. See "Viewing Object Data in Relational Form with Unnesting Queries".

### Performing DML Operations on Collections

Oracle supports the following DML operations on nested table columns:

- Inserts and updates that provide a new value for the entire collection

- Piecewise Updates

  - Inserting new elements into the collection

  - Deleting elements from the collection

  - Updating elements of the collection.

Oracle does not support piecewise updates on `VARRAY` columns. However, `VARRAY` columns can be inserted into or updated as an atomic unit.

For piecewise updates of nested table columns, the DML statement identifies the nested table value to be operated on by using the `TABLE` expression.

The DML statements in Example 3-20 demonstrate piecewise operations on nested table columns.

Example 3-20 Piecewise Operations on Collections

    INSERT INTO TABLE(SELECT d.dept_emps
                      FROM department_persons d
                      WHERE d.dept_no = 101)
       VALUES (5, 'Kevin Taylor', '1-800-555-6212');

    UPDATE TABLE(SELECT d.dept_emps
                      FROM department_persons d
                      WHERE d.dept_no = 101) e
       SET VALUE(e) = person_typ(5, 'Kevin Taylor', '1-800-555-6233')
       WHERE e.idno = 5;

    DELETE FROM TABLE(SELECT d.dept_emps
                      FROM department_persons d
                      WHERE d.dept_no = 101) e
       WHERE e.idno = 5;

Example 3-21 shows `VALUE` used to return object instance rows for updating:

Example 3-21 Using VALUE to Update a Nested Table

    UPDATE TABLE(SELECT d.dept_emps FROM department_persons d
                   WHERE  d.dept_no = 101) p
       SET VALUE(p) = person_typ(2, 'Diane Smith', '1-800-555-1243')
       WHERE p.idno = 2;

#### Performing DML on Multilevel Collections

For multilevel nested table collections, DML can be done atomically, on the collection as a whole, or piecewise, on selected elements. For multilevel varray collections, DML operations can be done only atomically.

##### Collections as Atomic Data Items

The section "Constructors for Multilevel Collections" shows an example of inserting an entire multilevel collection with an `INSERT` statement. Multilevel collections can also be updated atomically with an `UPDATE` statement. For example, suppose `v_country` is a variable declared to be of the countries nested table type `nt_country_typ`. Example 3-22 updates `region_tab` by setting the `countries` collection as a unit to the value of `v_country`.

Example 3-22 Using UPDATE to Insert an Entire Multilevel Collection

    INSERT INTO region_tab (region_id, region_name) VALUES(2, 'Americas');

    DECLARE
      v_country nt_country_typ;
    BEGIN
      v_country :=  nt_country_typ( country_typ(
       'US', 'United States of America', nt_location_typ (
       location_typ( 1500,'2011 Interiors Blvd','99236','San Francisco','California'),
       location_typ(1600,'2007 Zagora St','50090','South Brunswick','New Jersey'))));
      UPDATE region_tab r
        SET r.countries = v_country WHERE r.region_id = 2;
    END;
    /

##### Piecewise Operations on Nested Tables

Piecewise DML is possible only on nested tables, not on varrays.

Example 3-23 shows a piecewise insert operation on the `countries` nested table of nested tables. The example inserts a new country, complete with its own nested table of `location_typ`:

Example 3-23 Piecewise INSERT on a Multilevel Collection

    INSERT INTO TABLE( SELECT countries FROM region_tab r WHERE r.region_id = 2)
      VALUES ( 'CA', 'Canada', nt_location_typ(
           location_typ(1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario')));

Example 3-24 performs a piecewise insert into an inner nested table to add a location for a country. Like the preceding, this example uses a `TABLE` expression containing a subquery that selects the inner nested table to specify the target for the insert.

Example 3-24 Piecewise INSERT into an Inner Nested Table

    INSERT INTO TABLE( SELECT c.locations
      FROM TABLE( SELECT r.countries FROM region_tab r WHERE r.region_id = 2) c
      WHERE c.country_id = 'US')
      VALUES (1700, '2004 Lakeview Rd', '98199', 'Seattle', 'Washington');

    SELECT r.region_name, c.country_name, l.location_id
      FROM region_tab r, TABLE(r.countries) c, TABLE(c.locations) l;

### Comparisons of Collections

The conditions listed in this section allow comparisons of nested tables. There is no mechanism for comparing varrays. The SQL examples in this section use the nested tables created in Example 3-4.

#### Equal and Not Equal Comparisons

The equal (=) and not equal (<>) conditions determine whether the input nested tables are identical or not, returning the result as a Boolean value.

Two nested tables are equal if they have the same named type, have the same cardinality, and their elements are equal. Elements are equal depending on whether they are equal by the elements own equality definitions, except for object types which require a map method.

Example 3-25 Using an Equality Comparison with Nested Tables

    SELECT p.name
      FROM students, TABLE(physics_majors) p
    WHERE math_majors = physics_majors;

In Example 3-25, the nested tables contain `person_typ` objects which have an associated map method. See Example 2-1.

#### In Comparisons

The `IN` condition checks whether a nested table is in a list of nested tables, returning the result as a Boolean value. NULL is returned if the nested table is a null nested table.

Example 3-26 Using an IN Comparison with Nested Tables

    SELECT p.idno, p.name
      FROM students, TABLE(physics_majors) p
    WHERE physics_majors IN (math_majors, chem_majors);

#### Subset of Multiset Comparison

The `SUBMULTISET` `[OF]` condition checks whether a nested table is a subset of a another nested table, returning the result as a Boolean value. The `OF` keyword is optional and does not change the functionality of `SUBMULTISET`.

This operator is implemented only for nested tables because this is a multiset function only.

Example 3-27 Testing the SUBMULTISET OF Condition on a Nested Table

    SELECT p.idno, p.name
      FROM students, TABLE(physics_majors) p
    WHERE physics_majors SUBMULTISET OF math_majors;

#### Member of a Nested Table Comparison

The `MEMBER` `[OF]` or `NOT` `MEMBER` `[OF]` condition tests whether an element is a member of a nested table, returning the result as a Boolean value. The `OF` keyword is optional and has no effect on the output.

Example 3-28 Using MEMBER OF on a Nested Table

    SELECT graduation
      FROM students
    WHERE person_typ(12, 'Bob Jones', '1-800-555-1212') MEMBER OF math_majors;

In Example 3-28, `person_typ (12, 'Bob Jones', '1-800-555-1212')` is an element of the same type as the elements of the nested table `math_majors`.

#### Empty Comparison

The `IS` `[NOT]` `EMPTY` condition checks whether a given nested table is empty or not empty, regardless of whether any of the elements are NULL. If a NULL is given for the nested table, the result is NULL. The result is returned as a Boolean value.

Example 3-29 Using IS NOT on a Nested Table

    SELECT p.idno, p.name
      FROM students, TABLE(physics_majors) p
    WHERE physics_majors IS NOT EMPTY;

#### Set Comparison

The `IS` `[NOT]` `A` `SET` condition checks whether a given nested table is composed of unique elements, returning a Boolean value.

Example 3-30 Using IS A SET on a Nested Table

    SELECT p.idno, p.name
      FROM students, TABLE(physics_majors) p
    WHERE physics_majors IS A SET;

### Multisets Operations

This section describes multiset operations with nested tables. For a description of additional operations, see "Comparisons of Objects, REF Variables, and Collections". The SQL examples in this section use the nested tables created in Example 3-4.

For more information about using operators with nested tables, see Oracle Database SQL Reference.

#### CARDINALITY

The `CARDINALITY` function returns the number of elements in a varray or nested table. The return type is `NUMBER`. If the varray or nested table is a null collection, `NULL` is returned.

Example 3-31 Determining the CARDINALITY of a Nested Table

    SELECT CARDINALITY(math_majors)
      FROM students;

For more information about the `CARDINALITY` function, see Oracle Database SQL Reference.

#### COLLECT

The `COLLECT` function is an aggregate function which would create a multiset from a set of elements. The function would take a column of the element type as input and create a multiset from rows selected. To get the results of this function you must use it within a `CAST` function to specify the output type of `COLLECT`. See "CAST" for an example of the `COLLECT` function.

For more information about the `COLLECT` function, see Oracle Database SQL Reference.

#### MULTISET EXCEPT

The `MULTISET` `EXCEPT` operator inputs two nested tables and returns a nested table whose elements are in the first nested table but not in the second nested table. The input nested tables and the output nested table are all type name equivalent.

The `ALL` or `DISTINCT` options can be used with the operator. The default is `ALL`.

- With the `ALL` option, for `ntab1` `MULTISET` `EXCEPT` `ALL` `ntab2`, all elements in `ntab1` other than those in `ntab2` would be part of the result. If a particular element occurs `m` times in `ntab1` and `n` times in `ntab2`, the result will have (`m` - `n`) occurrences of the element if `m` is greater than `n` otherwise `0` occurrences of the element.

- With the `DISTINCT` option, any element that is present in `ntab1` which is also present in `ntab2` would be eliminated, irrespective of the number of occurrences.

Example 3-32 Using the MULTISET EXCEPT Operation on Nested Tables

    SELECT math_majors MULTISET EXCEPT physics_majors
      FROM students
    WHERE graduation = '01-JUN-03';

For more information about the `MULTISET` `EXCEPT` operator, see Oracle Database SQL Reference.

#### MULTISET INTERSECTION

The `MULTISET` `INTERSECT` operator returns a nested table whose values are common in the two input nested tables. The input nested tables and the output nested table are all type name equivalent.

There are two options associated with the operator: `ALL` or `DISTINCT`. The default is `ALL`. With the `ALL` option, if a particular value occurs `m` times in `ntab1` and `n` times in `ntab2`, the result would contain the element `MIN`(`m`, `n`) times. With the `DISTINCT` option the duplicates from the result would be eliminated, including duplicates of `NULL` values if they exist.

Example 3-33 Using the MULTISET INTERSECT Operation on Nested Tables

    SELECT math_majors MULTISET INTERSECT physics_majors
      FROM students
    WHERE graduation = '01-JUN-03';

For more information about the `MULTISET` `INTERSECT` operator, see Oracle Database SQL Reference.

#### MULTISET UNION

The `MULTISET` `UNION` operator returns a nested table whose values are those of the two input nested tables. The input nested tables and the output nested table are all type name equivalent.

There are two options associated with the operator: `ALL` or `DISTINCT`. The default is `ALL`. With the `ALL` option, all elements that are in `ntab1` and `ntab2` would be part of the result, including all copies of `NULL`s. If a particular element occurs `m` times in `ntab1` and `n` times in `ntab2`, the result would contain the element (`m` + `n`) times. With the `DISTINCT` option the duplicates from the result are eliminated, including duplicates of `NULL` values if they exist.

Example 3-34 Using the MULTISET UNION Operation on Nested Tables

    SELECT math_majors MULTISET UNION DISTINCT physics_majors
      FROM students
    WHERE graduation = '01-JUN-03';

`PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', '1-800-555-1212'),`
`PERSON_TYP(31, 'Sarah Chen', '1-800-555-2212'),`
`PERSON_TYP(45, 'Chris Woods', '1-800-555-1213'))`

    SELECT math_majors MULTISET UNION ALL physics_majors
      FROM students
    WHERE graduation = '01-JUN-03';

`PEOPLE_TYP(PERSON_TYP(12, 'Bob Jones', '1-800-555-1212'),`
`PERSON_TYP(31, 'Sarah Chen', '1-800-555-2212'),`
`PERSON_TYP(45, 'Chris Woods', '1-800-555-1213'),`
`PERSON_TYP(12, 'Bob Jones', '1-800-555-1212'),`
`PERSON_TYP(45, 'Chris Woods', '1-800-555-1213'))`

For more information about the `MULTISET` `UNION` operator, see Oracle Database SQL Reference.

#### POWERMULTISET

The `POWERMULTISET` function generates all non-empty submultisets from a given multiset. The input to the `POWERMULTISET` function could be any expression which evaluates to a multiset. The limit on the cardinality of the multiset argument is 32.

Example 3-35 Using the POWERMULTISET Operation on Multiset

    SELECT * FROM TABLE(POWERMULTISET( people_typ (
               person_typ(12, 'Bob Jones', '1-800-555-1212'),
               person_typ(31, 'Sarah Chen', '1-800-555-2212'),
               person_typ(45, 'Chris Woods', '1-800-555-1213'))));

For more information about the `POWERMULTISET` function, see Oracle Database SQL Reference.

#### POWERMULTISET_BY_CARDINALITY

The `POWERMULTISET_BY_CARDINALITY` function returns all non-empty submultisets of a nested table of the specified cardinality. The output would be rows of nested tables.

`POWERMULTISET_BY_CARDINALITY(x, l)` is equivalent to `TABLE(POWERMULTISET(x))` `p` where `CARDINALITY(value(p))` `=` `l`, where `x` is a multiset and l is the specified cardinality.

The first input parameter to the `POWERMULTISET_BY_CARDINALITY` could be any expression which evaluates to a nested table. The length parameter should be a positive integer, otherwise an error will be returned. The limit on the cardinality of the nested table argument is 32.

Example 3-36 Using the POWERMULTISET_BY_CARDINALITY Function

    SELECT * FROM TABLE(POWERMULTISET_BY_CARDINALITY( people_typ (
               person_typ(12, 'Bob Jones', '1-800-555-1212'),
               person_typ(31, 'Sarah Chen', '1-800-555-2212'),
               person_typ(45, 'Chris Woods', '1-800-555-1213')),2));

For more information about the `POWERMULTISET_BY_CARDINALITY` function, see Oracle Database SQL Reference.

#### SET

The `SET` function converts a nested table into a set by eliminating duplicates, and returns a nested table whose elements are `DISTINCT` from one another. The nested table returned is of the same named type as the input nested table.

Example 3-37 Using the SET Function on a Nested Table

    SELECT SET(physics_majors)
      FROM students
    WHERE graduation = '01-JUN-03';

For more information about the `SET` function, see Oracle Database SQL Reference.